<?php //
	/**
	 * Add a widget to the dashboard
	 */
	function cpx_analytics()
	{
		wp_add_dashboard_widget('cpx_analytics', // Widget slug.
			'CouponX Right Now', // Title.
			'cpx_analytics_function' // Display function.
			);
	}
        
    //if(is_admin() && current_user_can('administrator') === true )
	   add_action('wp_dashboard_setup', 'cpx_analytics');
	/**
	 * Create the function to output the contents of our Dashboard Widget.
	 */
	function cpx_analytics_function()
	{
	   $obj = cal_st_cp();
       $cp_not_rated = get_cp_not_rated();
       
       $cp_not_select_store = get_cp_not_select_store();
       $duplicate_st_name = get_duplicate_storename();
       $st_not_have_afflink = get_st_not_have_afflink();
       $st_not_have_logo = get_st_not_have_logo();
       $st_not_have_homeurl = get_st_not_have_homepage_url();
       $posts_have_special_characters = get_post_have_special_characters();
       $dup_stname_logoname = get_duplicate_storename_logo();
       ?>
        <ul>
            <li>
                <a href="<?php echo admin_url("/edit.php?post_type=store"); ?>"><p>Stores</p></a>
                <?php 
                    echo $obj->st_all.' total | ';
                    echo $obj->st_pl.' published | ';
                    echo $obj->st_dr.' draft | ';
                    echo $obj->st_pd.' pending';    
                ?>
            </li>
            <hr />
            <li>
                <a href="<?php echo admin_url("/edit.php?post_type=coupon"); ?>"><p>Coupons</p></a>
                <?php 
                    echo $obj->cp_all.' total | ';
                    echo $obj->cp_pl.' published | '; 
                    echo $obj->cp_dr.' draft | ';
                    echo $obj->cp_pd.' pending';    
                ?>
            </li>
            <hr />
            <?php
            printdata($cp_not_rated,'coupons not rated');
            printdata($cp_not_select_store,'coupons not select store');
            printdata($duplicate_st_name,'dupalicate store name');
            printdata($st_not_have_afflink,'stores not have affiliate link');
            printdata($st_not_have_logo,'stores not have logo image');
            printdata($st_not_have_homeurl,'stores not have home page url');
            printdata($posts_have_special_characters,'posts have special characters'); 
            printdata($dup_stname_logoname,'posts have same post name',0); 
            ?>
        </ul>
    <?php
	}
    function printdata($data, $title)
    { 
        if(count($data) > 0):
        ?>
        <li>
                <p><?php echo count($data);?><?php echo ' '.$title; ?></p>
                <?php foreach ($data as $k=>$c): ?>
                        <a title="<?php echo get_post_field('post_title', $c->ID); ?>" href="<?php echo admin_url()."post.php?post={$c->ID}&action=edit" ;?>"><?php echo $c->ID; ?></a>
                        <?php if($k != count($data)-1) echo " | "; ?>
                <?php endforeach; ?>
        </li>
        <hr />
    <?php
        endif;
    }
    /**
     * FUNCTIONS
     */
    function cal_st_cp()
    {
        global $wpdb;
        $query = "
        SELECT 
            SUM(CASE WHEN post_type = 'store' AND post_status IN ('publish','draft','pending') THEN 1 ELSE 0 END) AS 'st_all',
        	SUM(CASE WHEN post_status = 'publish' AND post_type = 'store' THEN 1 ELSE 0 END) AS 'st_pl',
        	SUM(CASE WHEN post_status = 'draft' AND post_type = 'store' THEN 1 ELSE 0 END) AS 'st_dr',
        	SUM(CASE WHEN post_status = 'pending' AND post_type = 'store' THEN 1 ELSE 0 END) AS 'st_pd',
            SUM(CASE WHEN post_type = 'coupon' AND post_status IN ('publish','draft','pending') THEN 1 ELSE 0 END) AS 'cp_all',
        	SUM(CASE WHEN post_status = 'publish' AND post_type = 'coupon' THEN 1 ELSE 0 END) AS 'cp_pl',
        	SUM(CASE WHEN post_status = 'draft' AND post_type = 'coupon' THEN 1 ELSE 0 END) AS 'cp_dr',
        	SUM(CASE WHEN post_status = 'pending' AND post_type = 'coupon' THEN 1 ELSE 0 END) AS 'cp_pd'
        FROM {$wpdb->posts}
        ";
        $obj = $wpdb->get_row($query);
        return $obj;
    }
    function get_cp_not_rated()
    {
        global $wpdb;
        $query = "
        SELECT ID FROM {$wpdb->posts} WHERE post_type IN ('coupon') AND post_status IN ('publish','pending') AND ID NOT IN 
        (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = 'cp_rate_metadata');
        ";        
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_cp_not_select_store()
    {
        global $wpdb;
        $query = "
        SELECT * FROM {$wpdb->posts} WHERE post_type IN ('coupon') AND post_status IN ('publish','pending') AND ID NOT IN 
        (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = 'store_coupon_metadata');
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_st_not_have_cp($have_cp = 'no')
    {
        global $wpdb;
        if($have_cp == 'no')
            $query = "
            SELECT ID FROM {$wpdb->posts} WHERE post_type = 'store' AND post_status = 'publish' AND ID NOT IN 
            (SELECT meta_value FROM {$wpdb->postmeta}  WHERE meta_key = 'store_coupon_metadata' GROUP BY meta_value) ORDER BY ID;
            ";
        else if($have_cp == 'yes')
            $query = "
            SELECT ID FROM {$wpdb->posts} WHERE post_type = 'store' AND post_status = 'publish' AND ID IN 
            (SELECT meta_value FROM {$wpdb->postmeta}  WHERE meta_key = 'store_coupon_metadata' GROUP BY meta_value) ORDER BY ID;
            ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_duplicate_storename()
    {
        global $wpdb;
        $query = "
        SELECT ID FROM {$wpdb->posts} WHERE post_type = 'store' AND (post_name LIKE '%2' OR post_name LIKE '%3' );
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_st_not_have_afflink()
    {
        global $wpdb;
        $query = "
        SELECT ID FROM {$wpdb->posts} WHERE post_type = 'store' AND post_status = 'publish' AND ID NOT IN (SELECT post_id FROM {$wpdb->postmeta} WHERE (meta_key = 'aff_url_store_metadata' AND meta_value != '' ) )
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_st_not_have_logo()
    {
        global $wpdb;
        $query = "
        SELECT ID FROM {$wpdb->posts} WHERE post_type = 'store' AND post_status = 'publish'
        AND ID NOT IN
        (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key='logo_metadata');
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_st_not_have_homepage_url()
    {
        global $wpdb;
        $query = "
        SELECT ID FROM {$wpdb->posts} WHERE post_type = 'store' AND post_status = 'publish'
        AND ID NOT IN
        (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key='url_store_metadata');
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_post_have_special_characters()
    {
        global $wpdb;
        $query = "
        SELECT ID FROM {$wpdb->posts} WHERE post_content LIKE '%<%' AND post_type IN('store','coupon') AND post_status = 'publish'
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }
    function get_duplicate_storename_logo()
    {
        global $wpdb;
        $query = "
        SELECT ID,post_name, COUNT(*) c FROM {$wpdb->posts}
        WHERE post_name NOT LIKE '%revision%' 
        AND post_name NOT LIKE '%autosave%'
        AND post_name != ''
        AND post_status != 'trash'
        GROUP BY post_name HAVING c > 1;
        ";
        $rs = $wpdb->get_results($query);
        return $rs;
    }